package com.heimali.happyreader.db.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.heimali.happyreader.db.DBHelper;
import com.heimali.happyreader.model.Intro;
import com.heimali.happyreader.utils.SystemUtils;

public class ListDao extends BaseDao {
	private static final String TAG = "ListDao";

	public ListDao() {
		tableName = "list";
	}

	public int insert(List<Intro> list) {
		int count = 0;
		if (list.size() == 0) {
			return count;
		}
		synchronized (DBHelper.writeLock) {
			SQLiteDatabase db = DBHelper.getDatabase();
			try {
				String sql = "insert into " + getTable() + " (articleID, articleTime, title, intro) values (?, ?, ?, ?)";
				db.beginTransaction();
				for (Intro item : list) {
					db.execSQL(sql, new Object[] { item.id, item.time, item.title, item.intro });
					count++;
				}
				db.setTransactionSuccessful();
			} catch (SQLException e) {
				e.printStackTrace();
				SystemUtils.Log(TAG, e.getMessage());
			} finally {
				if (db != null) {
					db.endTransaction();
				}
			}
		}
		return count;
	}

	public int insert(Intro item) {
		int count = 0;
		if (item == null) {
			return count;
		}
		synchronized (DBHelper.writeLock) {
			SQLiteDatabase db = DBHelper.getDatabase();
			try {
				String sql = "insert into " + getTable() + " (articleID, articleTime, title, intro) values (?, ?, ?, ?)";
				db.execSQL(sql, new Object[] { item.id, item.time, item.title, item.intro });
				count++;
			} catch (SQLException e) {
				e.printStackTrace();
				SystemUtils.Log(TAG, e.getMessage());
			}
		}
		return count;
	}


	public List<Intro> query(int start, int count) {
		List<Intro> list = new ArrayList<Intro>();
		SQLiteDatabase db = DBHelper.getDatabase();
		Cursor cursor = null;
		
		if (start < 0) {
			start = 0;
		}
		if (count <= 0) {
			count = 1;
		}
		String sql = "select articleID,articleTime,title,intro from " + getTable() + " order by articleID desc limit " + start + "," + count;
		try {
			cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				list.add(new Intro(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			SystemUtils.Log(TAG, e.getMessage());
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return list;
	}
	
	/**
	 * 倒序查询，从小于articleID的值开始，取count条
	 * @param articleID
	 * @param count
	 * @return
	 */
	public List<Intro> queryEarlyByArticleID(int articleID, int count){
		return queryEarlyByArticleID(articleID, count, "desc");
	}
	
	public List<Intro> queryEarlyByArticleID(int articleID, int count, String order) {
		List<Intro> list = new ArrayList<Intro>();
		SQLiteDatabase db = DBHelper.getDatabase();
		Cursor cursor = null;
		
		if (count <= 0) {
			count = 1;
		}
		
		order = "desc".equals(order) ? "desc" : "asc";
		String sql = "select articleID,articleTime,title,intro from " + getTable() + " where articleID < " + articleID + " order by articleID "+ order +" limit " + count;
		try {
			cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				list.add(new Intro(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			SystemUtils.Log(TAG, e.getMessage());
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return list;
	}
	
	public List<Intro> queryRecentByArticleID(int articleID, int count) {
		return queryRecentByArticleID(articleID, count, "asc");
	}
	
	public List<Intro> queryRecentByArticleID(int articleID, int count, String order) {
		List<Intro> list = new ArrayList<Intro>();
		SQLiteDatabase db = DBHelper.getDatabase();
		Cursor cursor = null;
		
		if (count <= 0) {
			count = 1;
		}
		
		order = "desc".equals(order) ? "desc" : "asc";
		String sql = "select articleID,articleTime,title,intro from " + getTable() + " where articleID > " + articleID + " order by articleID "+ order +" limit " + count;
		try {
			cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				list.add(new Intro(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			SystemUtils.Log(TAG, e.getMessage());
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return list;
	}
	
	public int queryMinArticleID() {
		return queryLastArticleID("asc");
	}
	
	public int queryMaxArticleID() {
		return queryLastArticleID("desc");
	}
	
	/**
	 * 按顺序查询最后一篇文章的id
	 * @param order : "desc" or "asc" (default) 
	 * @return id
	 */
	public int queryLastArticleID(String order) {
		order = "asc".equals(order) ? "asc" : "desc";
		int lastID = 0;
		String sql = "select articleID from " + getTable() + " order by articleID " + order + " limit 1";
		SQLiteDatabase db = DBHelper.getDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, null);
			while(cursor.moveToNext()) {
				lastID = cursor.getInt(0);
			}
			cursor.close();
		}catch(SQLException e) {
			e.printStackTrace();
			SystemUtils.Log(TAG, e.getMessage());
		}finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return lastID;
	}
	
	public List<Integer> queryUnSyncArticleIDs (int count) {
		List<Integer> list = new ArrayList<Integer>();
		SQLiteDatabase db = DBHelper.getDatabase();
		Cursor cursor = null;
		
		if (count <= 0) {
			count = 1;
		}
		
		String sql = "select articleID from " + getTable() + " where isDownload=0 order by articleID desc limit 0,"+count;
		//SystemUtils.Log(TAG, "queryUnSyncArticleIDs", count, sql);
		try {
			cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				list.add(cursor.getInt(0));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			SystemUtils.Log(TAG, e.getMessage());
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return list;
	}
	
	public void insertNewRecord(List<Intro> list) {
		String querySql = "select articleID from " + getTable() + " where articleID in (";
		HashMap<Integer, Intro> map = new HashMap<Integer, Intro>();
		for (Intro item : list) {
			map.put(item.id, item);
			querySql += item.id + ",";
		}
		querySql = querySql.substring(0, querySql.length()-2) + ")";
		
		
		SQLiteDatabase db = DBHelper.getDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(querySql, null);
			while(cursor.moveToNext()) {
				map.remove(cursor.getInt(0));
			}
			cursor.close();
		}catch(SQLException e) {
			e.printStackTrace();
			SystemUtils.Log(TAG, e.getMessage());
		}finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		insert(new ArrayList<Intro>(map.values()));
	}
	
	public void updateIsDownload(int articleID) {
		String sql = "update list set isDownload=1 where articleID="+articleID;
		SQLiteDatabase db = DBHelper.getDatabase();
		db.execSQL(sql);
	}
	
	public void updateIsFav(int articleID) {
		String sql = "update list set isFav=1 where articleID="+articleID;
		SQLiteDatabase db = DBHelper.getDatabase();
		db.execSQL(sql);
	}
}
